package com.itcast.jdbc;

import com.itcast.domain.Emp;
import com.itcast.util.jdbcUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 练习：
 * 定义一个方法，查询 emp 表中的数据将其封装为对象，然后装载集合，打印。
 */
public class jdbcDemo8 {
    public static void main(String[] args) {

//        List<Emp> arr = findAll();
        List<Emp> arr = findAll2();
        System.out.println(arr);
    }

    /**
     * 查询所有的 emp 对象
     *
     * @return
     */
    public static List<Emp> findAll() {
        List<Emp> arr = null;
        Connection connection = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            // 1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 2.连接对象
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
            // 3.定义 sql
            String sql = "SELECT * FROM emp";
            // 4.执行对象
            stmt = connection.createStatement();
            // 5.执行 sql
            rs = stmt.executeQuery(sql);
            // 6. 处理结果
            Emp emp = null;
            arr = new ArrayList<Emp>();
            while (rs.next()) {
                // 获取数据
                int id = rs.getInt("id");
                String ename = rs.getString("ename");
                int jobId = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                Date joindate = rs.getDate("joindate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int deptId = rs.getInt("dept_id");

                // 创建 Emp 对象，并赋值
                emp = new Emp(id, ename, jobId, mgr, joindate, salary, bonus, deptId);

                // 装载集合
                arr.add(emp);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return arr;
    }

    /**
     * 演示 jdbc 工具类
     *
     * @return
     */
    public static List<Emp> findAll2() {
        List<Emp> arr = null;
        Connection connection = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            /*// 1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 2.连接对象
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");*/
            connection = jdbcUtils.getConnection();
            // 3.定义 sql
            String sql = "SELECT * FROM emp";
            // 4.执行对象
            stmt = connection.createStatement();
            // 5.执行 sql
            rs = stmt.executeQuery(sql);
            // 6. 处理结果
            Emp emp = null;
            arr = new ArrayList<Emp>();
            while (rs.next()) {
                // 获取数据
                int id = rs.getInt("id");
                String ename = rs.getString("ename");
                int jobId = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                Date joindate = rs.getDate("joindate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int deptId = rs.getInt("dept_id");

                // 创建 Emp 对象，并赋值
                emp = new Emp(id, ename, jobId, mgr, joindate, salary, bonus, deptId);

                // 装载集合
                arr.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(rs, stmt, connection);
            /*if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }*/
        }

        return arr;
    }
}
